package com.core.shared;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @author lxf
 * @date 2018/3/15 17:02
 * To change this template use Editor | File and Code Templates.
 */
public class CallCrocedure {

    /**
     * 调用短信发送存储过程
     *
     * @return
     */
    public static String callCrocedure() {
        // 对方数据库存储过程进行了包装
        String sql = "{call pk_sms.sp_send_msg(?,?,?,?,?,?,?)}";
        List<String> inParams = new ArrayList<String>();
        inParams.add("params");
        inParams.add("params");
        inParams.add("1");
        inParams.add("params");
        inParams.add("params");
        String outParam = "";
        Connection conn = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        try {
            conn = getAMCConnection();
            cs = conn.prepareCall(sql);
            cs.setString(1, inParams.get(0));
            cs.setString(2, inParams.get(1));
            cs.setInt(3, Integer.parseInt(inParams.get(2)));
            cs.setInt(4, Integer.parseInt(inParams.get(3)));
            cs.setString(5, inParams.get(4));
//            cs.registerOutParameter(6, OracleTypes.VARCHAR);
//            cs.registerOutParameter(7, OracleTypes.VARCHAR);    //返回结果说明，不需要进行处理
            cs.execute();
            outParam = cs.getString(6);
            cs.close();
            conn.close(); // Return to connection pool
            conn = null; // Make sure we don't close it twice
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (cs != null) {
                try {
                    cs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
        return outParam;
    }

    private static final Connection getAMCConnection() throws SQLException {
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@172.18.21.60:1521:WEBDB", "username", "password");
        } catch (java.lang.ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        return conn;
    }

}
